package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import util.Number;
import model.City;
import model.Country;
import model.SubContractor;
import model.SubContractorJob;

public class DBSubContractor extends DBOutsider implements IFSubContractor
{
	private Connection con;

	public DBSubContractor()
	{
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public SubContractor searchSubContractorID(int subContractorID) throws SQLException
	{
		ResultSet results;
		SubContractor newSubContractor = null;

		String query = "SELECT o.*, c.city_name, cn.country_name, j.outsider_job_name FROM outsider AS o LEFT JOIN city AS c ON o.outsider_city_id = c.city_id LEFT JOIN country AS cn ON c.city_country_id = cn.country_id LEFT JOIN outsider_job AS j ON o.outsider_job_id = j.outsider_job_id WHERE o.outsider_id = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("searchSubContractorID", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setInt(1, subContractorID);

			results = ps.executeQuery();

			while(results.next())
			{
				newSubContractor = buildSubContractorObject(results, false);
			}

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - searchSubContractorID - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return newSubContractor;
	}

	@Override
	public SubContractor searchSubContractorByName(String name) throws SQLException
	{
		ResultSet results;
		SubContractor newSubContractor = null;

		String query = "SELECT o.*, c.city_name, cn.country_name, j.outsider_job_name FROM outsider AS o LEFT JOIN city AS c ON o.outsider_city_id = c.city_id LEFT JOIN country AS cn ON c.city_country_id = cn.country_id LEFT JOIN outsider_job AS j ON o.outsider_job_id = j.outsider_job_id WHERE o.outsider_name = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("searchSubContractorID", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, name);

			results = ps.executeQuery();

			while(results.next())
			{
				newSubContractor = buildSubContractorObject(results, false);
			}

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - searchSubContractorByName - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return newSubContractor;
	}
	
	@Override
	public ArrayList<SubContractor> searchSubContractorEverything(boolean active, String jobName, String search) throws SQLException
	{
		boolean number = Number.isNumber(search);

		return searchSubContractors(active, jobName, search, "(o.outsider_name LIKE ? OR o.outsider_phoneno LIKE ? OR o.outsider_email LIKE ? OR c.city_name LIKE ? OR cn.country_name LIKE ? OR j.outsider_job_name LIKE ? " + (number ? " OR o.outsider_id = ?" : "") + " )", (number ? 7 : 6));
	}

	@Override
	public ArrayList<SubContractor> searchSubContractorName(boolean active, String jobName, String search) throws SQLException
	{
		return searchSubContractors(active, jobName, search, "o.outsider_name LIKE ?", 1);
	}

	@Override
	public ArrayList<SubContractor> searchSubContractorEmail(boolean active, String jobName, String search) throws SQLException
	{
		return searchSubContractors(active, jobName, search, "o.outsider_email LIKE ?", 1);
	}

	@Override
	public ArrayList<SubContractor> searchSubContractorPhoneNo(boolean active, String jobName, String search) throws SQLException
	{
		return searchSubContractors(active, jobName, search, "o.outsider_phoneno LIKE ?", 1);
	}

	@Override
	public ArrayList<SubContractor> searchSubContractorCity(boolean active, String jobName, String search) throws SQLException
	{
		return searchSubContractors(active, jobName, search, "c.city_name LIKE ?", 1);
	}

	@Override
	public ArrayList<SubContractor> searchSubContractorCountry(boolean active, String jobName, String search) throws SQLException
	{
		return searchSubContractors(active, jobName, search, "cn.country_name LIKE ?", 1);
	}
	
	@Override
	public ArrayList<SubContractor> searchSubContractorAll()
	{
		ArrayList<SubContractor> subList = new ArrayList<SubContractor>();
		
		ResultSet results;
		SubContractor newSubContractor = null;

		String query = "SELECT outsider_name FROM outsider WHERE outsider_job_id IS NOT NULL AND outsider_active = 1 ORDER BY outsider_name ASC";

		if (DBConnection.doDebug())
			DBConnection.printDebug("searchSubContractorAll", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			results = ps.executeQuery();

			while(results.next())
			{
				newSubContractor = new SubContractor();
				newSubContractor.setName(results.getString("outsider_name"));
				
				subList.add(newSubContractor);
			}

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - searchSubContractorAll - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return subList;
	}

	/**
	 * Our method for handling sub contractor search
	 * 
	 * @param 	boolean		are we searching for active only or not
	 * @param 	String		the job type we are searching for, can be empty
	 * @param	String 		our search word, we use it for the prepared statement
	 * @param 	String		our extra query (in order to keep everything in one method and not 500 lines of code..)
	 * @param 	int			might be kinda hardcoded, number of prepared statements we have to do, most (only) used in searchSubContractorEverything
	 * 
	 * @return  ArrayList<SubContractor>
	 */
	private ArrayList<SubContractor> searchSubContractors(boolean active, String jobName, String search, String clause, int num)
	{
		ArrayList<SubContractor> subContractorList = new ArrayList<SubContractor>();

		String query = "SELECT o.*, c.city_name, cn.country_name, cn.country_tld, j.outsider_job_name FROM outsider AS o LEFT JOIN city AS c ON o.outsider_city_id = c.city_id LEFT JOIN country AS cn ON c.city_country_id = cn.country_id LEFT JOIN outsider_job AS j ON o.outsider_job_id = j.outsider_job_id WHERE o.outsider_job_id IS NOT NULL" + (active ? " AND o.outsider_active = '1'" : "") + (jobName.length() > 0 ? " AND j.outsider_job_name = '" + jobName + "' " : "") + ((clause.length() > 0) ? (" AND " + clause) : "") + " ORDER BY o.outsider_name ASC";

		if (DBConnection.doDebug())
			DBConnection.printDebug("searchSubContractors", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			if (clause.length() > 0)
			{
				ps.setString(1, "%" + search + "%");

				if (num > 1)
				{
					for(int i = 2; i <= num; i++)
						ps.setString(i, (i == 7 ? search : "%" + search + "%"));						
				}
			}

			ResultSet results = ps.executeQuery();

			while(results.next())
			{
				SubContractor newSubContractor = buildSubContractorObject(results, true);

				subContractorList.add(newSubContractor);
			}

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - searchSubContractors - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return subContractorList;
	}

	private SubContractor buildSubContractorObject(ResultSet results, boolean flag) throws SQLException
	{
		SubContractor newSubContractor = new SubContractor();

		newSubContractor.setOutsiderID(results.getInt("outsider_id"));
		newSubContractor.setName(results.getString("outsider_name"));
		newSubContractor.setActive(results.getBoolean("outsider_active"));
		newSubContractor.setEmail(results.getString("outsider_email"));
		newSubContractor.setPhoneNo(results.getString("outsider_phoneno"));
		newSubContractor.setCity(new City(results.getInt("outsider_city_id"), results.getString("city_name"), (flag ? (new Country(results.getString("country_name"), results.getString("country_tld"))) : (new Country(results.getString("country_name"))))));
		newSubContractor.setJob(new SubContractorJob(results.getInt("outsider_job_id"), results.getString("outsider_job_name")));

		return newSubContractor;
	}
}